package com.wugui.datax.admin.service.impl;

import com.wugui.datax.admin.entity.ColumnInfo;
import com.wugui.datax.admin.entity.DBInfo;
import com.wugui.datax.admin.entity.TableInfo;
import com.wugui.datax.admin.mapper.DBMapper;
import com.wugui.datax.admin.service.DBService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


@Service
public class DBServiceImpl implements DBService {

    private static Logger logger = LoggerFactory.getLogger(JobServiceImpl.class);

    @Resource
    private DBMapper dbMapper;

    @Override
    public List<DBInfo> list() {
        List<DBInfo> dbInfoList = new ArrayList<>();
        Connection conn = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn =  DriverManager.getConnection ("jdbc:mysql://localhost:3308/information_schema?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8", "root", "root");
            rs = conn.getMetaData().getCatalogs();

            while (rs.next()) {
                String dbName = rs.getString("TABLE_CAT");
                // 排查默认的四个库 information_schema、mysql、performance_schema、sys
                if ("information_schema".equals(dbName) || "mysql".equals(dbName) || "performance_schema".equals(dbName) || "sys".equals(dbName)){
                    continue;
                }
                DBInfo dbInfo = new DBInfo();
                dbInfo.setDBName(dbName);
                dbInfoList.add(dbInfo);
            }
        } catch (ClassNotFoundException|SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null){
                    rs.close();
                }
                if (conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        for (DBInfo dbinfo:dbInfoList) {
            System.out.println(dbinfo);
        }
        return dbInfoList;
    }

    @Override
    public List<TableInfo> tableList(String dbName) {
        String sql = "SELECT table_name,TABLE_ROWS,UPDATE_TIME\n" +
                "FROM information_schema.`TABLES`\n" +
                "WHERE \n" +
                " TABLE_TYPE = 'BASE TABLE' \n" +
                " AND table_schema = '"+dbName+"'";
        return tableInfoListQuery(dbName,sql);
    }

    @Override
    public List<TableInfo> tableSearchList(String dbName, String tableName, String collectWay, String status) {
        String sql = "SELECT table_name,TABLE_ROWS,UPDATE_TIME\n" +
                "FROM information_schema.`TABLES`\n" +
                "WHERE \n" +
                " TABLE_TYPE = 'BASE TABLE' \n" +
//                " AND table_schema NOT IN('information_schema','mysql','performance_schema','sys')\n" +
                " AND table_schema = '"+dbName+"'" +
                " AND table_name LIKE '%"+tableName+"%'";

        return tableInfoListQuery(dbName,sql);
    }

    @Override
    public List<ColumnInfo> tableColumnInfo(String dbName, String tableName) {

        return dbMapper.selectColumnInfo(dbName,tableName);
    }

    @Override
    public List<Object> tableData(String dbName, String tableName, int nums) {

//        return dbMapper.tableData(dbName,tableName,nums);
        return null;
    }

    public List<TableInfo> tableInfoListQuery(String dbName,String sql){
        List<TableInfo> tableInfoList = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            // information_schema
            String jdbcUrl = "jdbc:mysql://localhost:3308/"+dbName+"?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8";
            Connection conn =  DriverManager.getConnection (jdbcUrl, "root", "root");

            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                TableInfo tableInfo = new TableInfo();

                String tableName = resultSet.getString(1);
//                Long counts = dbMapper.counts(dbName, tableName);
//                System.out.println(tableName+"=="+counts);
                int counts = resultSet.getInt(2);
                Timestamp updateTime = resultSet.getTimestamp(3);

                tableInfo.setTableName(tableName);
                tableInfo.setCounts(Long.parseLong(counts+""));
                tableInfo.setUpdateTime(updateTime);
                // todo
//                tableInfo.setCollectWay(collectWay);
                tableInfoList.add(tableInfo);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableInfoList;
    }

    public static void main(String[] args) {
//        long l = Long.parseLong(17 + "");
//        System.out.println(l);

        DBService dbService = new DBServiceImpl();
        dbService.list();
//        String dbName = "datax_web";
//        List<TableInfo> tableInfos = dbService.tableList(dbName);
    }
}
